买买买,怎么样才最划算?Excel轻松帮你出方案!
Hi,我是秋小叶~
在生活中,人们总是想花最少的钱买到足够多的东西,但是实际中,理论上的利润最大化总会受到诸多限制。
如何突破困难找到最优方案呢?Excel 中的规划求解功能没准就能帮到你!
从 8 月开始,手机界的各位大佬开始了一波全面屏潮,NOTE8,MIX2,iPhone X 都长这样子:
不少土豪已经准备拿 4W 预算来购买新机送宠物,那问题来了:我们怎么花光这笔钱,买到最多的手机?
这种问题自然难不倒身为数学强国的我们,很简单,设 MIX2 数量为 X,NOTE8 数量为 Y, iPhone X 数量为 Z,总数量为 A,我们很快就能列出方程式:
4399X+6988Y+8388Z ≤ 40000
X+Y+Z = A
这个怎么解?这远远超出了我小学 3 年级的数学水平!其实类似情况在日常工作中还不少见!
求最优解的难题
比如在物流行业:
给你 N 条路线,让你找出最优的物流路线,而且快速送货的同时,还必须节约成本,请问我们该设几个未知数,才能求解最优路线?
比如在财务人员:
公司给了定额预算,要求我们根据公司所需的物质种类进行购买,在有最低数量要求的情况下,购买最多的货物,请问我们该设几个未知数,才能得到最佳购买方案?
比如税务人员:
N 张发票没有认证,在只知道总金额,但不清楚由哪几张发票组成,需从 N 条发票数据中找出符合的发票数,请问我们该设几个未知数,才能求解答案?
······
鲜为人知的规划求解
例子太多了,有没有觉得这些复杂的选择求解问题很让我们头疼?如果我告诉你,这些问题在 Excel 只要用一个功能就能轻松算出结果,你觉得可能吗?
不仅可能,还真有,它是 Excel 隐藏起来的神秘功能——规划求解。通过这个功能, Excel 可以自动帮你算出最优方案。
我们都知道,凡是值得藏起来的东西,都有着巨大的价值,这么强大的操作,是不是心动了呢?
首先,我们必须把藏得很深的规划求解功能,先调出来。
操作过程,如下图所示:
❶ 在 Excel 搜索框中输入「规划求解」,点击【加载项】
2013 及以下版本没有搜索框,依次单击如下菜单命令即可找到加载项窗口:【文件】-【选项卡】 -【选项】 -【加载项】 -【在弹窗最底下保持默认的Excel 加载项】【转到】
❷ 勾选【规划求解加载项】,点击【确定】
❸ 点击【数据】选项卡,我们可以看到规划求解按钮
规划求解功能已经挖出来了。下面就回到最初的问题,怎么利用 4 万块钱买到最多的手机?一共要花多少钱?
答案是:花费 39960 元。最佳购机方案为
1 款 iPhone X
2 款 NOTE8
4 款 MIX2
问题是,这个方案是怎么算出来的。对于规划求解来说,就是小菜一碟。
最优方案求解,快到不可思议
经过以下 4 个步骤,就可以自动算出最优方案。如果你觉得动图太快看不清楚,没关系,后面还有文字说明。
因为要让 Excel 自动算每一种方案的总共会花多少钱,再和购机总额 4 万块比对。Excel 才能从各种不同配置方案中,找出一个最接近的方案。于是我们需要先设置一个公式,计算出购机总共花费的金额。
❶ 预设总金额公式
在 B8 单元格输入=SUMPRODUCT(B3:B5,C3:C5),然后点击 规划求解 ,开始配置参数
SUMPRODUCT 函数是将两组对应数值进行相乘后相加的函数。用它会比一个个单元格输入算式更简单快捷。上面的函数公式相当于=B3*C3+B4*C4+B5*C5。
更多 SUMPRODUCT 函数的用法,参见文末的延伸阅读。
由于每一款手机的金额是固定的,Excel 要计算出一个最好的方案。那就得告诉 Excel,每一种方案总共会花多少钱,并且告诉它数量在哪里。
❷ 设置目标金额和数量区域
设置目标选择 B8 单元格,通过更改可变单元格 选择 C3:C5 区域,然后点击【添加】,打开约束条件窗口
目标金额单元格、数量两个参数,是规划求解中的变量,每一种方案会有所不同。
规划求解的本质,就是在一定的约束条件下计算出最优方案。所以我们还得得告诉 Excel,有哪些约束条件。在本案例中的约束条件有 2 个,总金额不能超过 40000,以及购机的数量必须为整数(整数用 int 表示)
买半个 iPhonex 也装不了 x 啊~
❸ 添加约束条件
添加约束条件一:在添加约束小窗中,单元格引用选择 B8,下拉选择 <=,输入 40000,点击【添加】;
继续添加约束条件二:单元格引用 选择 C3:C5 区域,选择【int】,点击【确定】,即完成所有条件设置。
❹ 查看结果
点击【求解】,即可查看最终的最优方案规划结果
就这样四步,轻易解决了刚才的高难度购机方案问题,是不是很爽快?很有成就感?学会这一招可以用在很多地方哦。
日常工作中,可能还会有更多复杂的条件,怎么办?别担心,小事一桩,规划求解依然能够帮你自动算出,你只需要继续添加更多约束条件就够了。
更多条件约束,也能自动给最优方案
对于土豪们来说,只买 1 部 iPhone X 是无法符合他们土豪气质,所以需要追加条件:iPhone X 必须要有 2 部以上。我就直接说答案了
最优购机方案:花费 39550 元,分别购买
2 部 iPhone X
2 部 MIX2
2 部 NOTE8
怎么做?只需要在前面操作步骤的基础上添加一个约束条件:
步骤很简单:
点击 规划求解,单击 添加 按钮,打开约束条件添加窗口;
添加条件 C3 >= 2,点击 确定;
点击 求解,查看结果
看到这里,规划求解到底有什么能耐和优势,你心里应该很清楚了。市场和采购的人员都对它爱不释手,物流的最优路线选择、供应链的管理应用、产品利润最大化等等都能派上用场。
约束条件下的最优解,就找规划求解
什么情况下规划求解能办到你呢?
❶ 多个未知因素,需要建立复杂的方程式组才能求解的问题,规划求解可以轻松解决
❷ 需求发生变化,可以通过增加或更改条件可以一键求出新的方案
最后的问题来了,对于三款手机,我选择使用:
我爸刚淘汰的 iPhone4
今日互动
第一次使用苹果,我该如何低调地发朋友圈?!
来评论区说说呗。急,在线等!!
▌关于本文
作者:晓东
本文由 秋叶PPT 原创发布,如需转载请邮件联系 zhuanzai@qiuyeppt.com
▌你可能对这些文章按兴趣
点击【阅读原文】,学习实用又高效的 Excel 技巧,比别人领先不止一个 Level!